package com.example.project.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.project.entity.IssueNotification;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * 需求通知Mapper接口
 */
@Mapper
public interface IssueNotificationMapper {

    /**
     * 根据用户ID获取通知列表
     */
    @Select("SELECT * FROM t_issue_notification WHERE user_id = #{userId} ORDER BY create_time DESC")
    List<IssueNotification> getNotificationsByUserId(@Param("userId") Long userId);

    /**
     * 根据用户ID获取未读通知列表
     */
    @Select("SELECT * FROM t_issue_notification WHERE user_id = #{userId} AND is_read = false ORDER BY create_time DESC")
    List<IssueNotification> getUnreadNotificationsByUserId(@Param("userId") Long userId);

    /**
     * 根据需求ID获取通知列表
     */
    @Select("SELECT * FROM t_issue_notification WHERE issue_id = #{issueId} ORDER BY create_time DESC")
    List<IssueNotification> getNotificationsByIssueId(@Param("issueId") Long issueId);

    /**
     * 根据通知类型获取通知列表
     */
    @Select("SELECT * FROM t_issue_notification WHERE notification_type = #{type} ORDER BY create_time DESC")
    List<IssueNotification> getNotificationsByType(@Param("type") String type);

    /**
     * 获取用户未读通知数量
     */
    @Select("SELECT COUNT(*) FROM t_issue_notification WHERE user_id = #{userId} AND is_read = false")
    Integer countUnreadNotificationsByUserId(@Param("userId") Long userId);

    /**
     * 获取用户的通知总数
     */
    @Select("SELECT COUNT(*) FROM t_issue_notification WHERE user_id = #{userId}")
    Integer countNotificationsByUserId(@Param("userId") Long userId);

    /**
     * 标记通知为已读
     */
    @Update("UPDATE t_issue_notification SET is_read = true WHERE id = #{notificationId}")
    int markAsRead(@Param("notificationId") Long notificationId);

    /**
     * 批量标记通知为已读
     */
    @Update("UPDATE t_issue_notification SET is_read = true WHERE user_id = #{userId} AND id IN #{notificationIds}")
    int batchMarkAsRead(@Param("userId") Long userId, @Param("notificationIds") List<Long> notificationIds);

    /**
     * 标记用户所有通知为已读
     */
    @Update("UPDATE t_issue_notification SET is_read = true WHERE user_id = #{userId}")
    int markAllAsReadByUserId(@Param("userId") Long userId);

    /**
     * 删除通知
     */
    @Update("UPDATE t_issue_notification SET deleted = true WHERE id = #{notificationId}")
    int deleteNotification(@Param("notificationId") Long notificationId);

    /**
     * 删除用户的所有已读通知
     */
    @Update("UPDATE t_issue_notification SET deleted = true WHERE user_id = #{userId} AND is_read = true")
    int deleteReadNotificationsByUserId(@Param("userId") Long userId);

    /**
     * 获取最近通知
     */
    @Select("SELECT * FROM t_issue_notification WHERE user_id = #{userId} ORDER BY create_time DESC LIMIT #{limit}")
    List<IssueNotification> getLatestNotificationsByUserId(@Param("userId") Long userId, @Param("limit") Integer limit);

    /**
     * 获取特定时间范围的通知
     */
    @Select("SELECT * FROM t_issue_notification WHERE user_id = #{userId} AND create_time >= #{startTime} AND create_time <= #{endTime}")
    List<IssueNotification> getNotificationsByTimeRange(@Param("userId") Long userId, @Param("startTime") String startTime, @Param("endTime") String endTime);

    /**
     * 获取需求相关用户的通知
     */
    @Select("SELECT n.* FROM t_issue_notification n " +
            "JOIN t_issue i ON n.issue_id = i.id " +
            "WHERE (i.author_id = #{userId} OR i.assigned_to_id = #{userId}) AND n.user_id != #{userId} " +
            "ORDER BY n.create_time DESC")
    List<IssueNotification> getRelatedNotificationsByUserId(@Param("userId") Long userId);

    /**
     * 批量插入通知
     */
    @Insert({
        "<script>",
        "INSERT INTO t_issue_notification (issue_id, receiver_id, notification_type, title, content, sender_id, is_read, create_time) VALUES ",
        "<foreach collection='list' item='item' separator=','>",
        "(#{item.issueId}, #{item.receiverId}, #{item.notificationType}, #{item.title}, #{item.content}, #{item.senderId}, #{item.isRead}, #{item.createTime})",
        "</foreach>",
        "</script>"
    })
    int batchInsert(@Param("list") List<IssueNotification> notifications);
    
    /**
     * 根据条件查询通知
     */
    @Select({
        "<script>",
        "SELECT * FROM t_issue_notification WHERE 1=1",
        "<if test='conditions.id != null'>",
        " AND id = #{conditions.id}",
        "</if>",
        "<if test='conditions.issueId != null'>",
        " AND issue_id = #{conditions.issueId}",
        "</if>",
        "<if test='conditions.receiverId != null'>",
        " AND receiver_id = #{conditions.receiverId}",
        "</if>",
        "<if test='conditions.notificationType != null'>",
        " AND notification_type = #{conditions.notificationType}",
        "</if>",
        "<if test='conditions.isRead != null'>",
        " AND is_read = #{conditions.isRead}",
        "</if>",
        " ORDER BY create_time DESC",
        "</script>"
    })
    List<IssueNotification> findByConditions(@Param("conditions") Map<String, Object> conditions);
    
    /**
     * 根据条件批量更新通知
     */
    @Update({
        "<script>",
        "UPDATE t_issue_notification SET ",
        "<if test='params.isRead != null'>",
        " is_read = #{params.isRead},",
        "</if>",
        "<if test='params.readTime != null'>",
        " read_time = #{params.readTime},",
        "</if>",
        " WHERE ",
        "<if test='params.receiverId != null'>",
        " receiver_id = #{params.receiverId}",
        "</if>",
        "<if test='params.userId != null'>",
        " receiver_id = #{params.userId}",
        "</if>",
        "<if test='params.isRead == null or params.isRead == 0'>",
        " AND is_read = 0",
        "</if>",
        "</script>"
    })
    int batchUpdateByCondition(@Param("params") Map<String, Object> params);
    
    /**
     * 根据条件批量删除通知
     */
    @Update({
        "<script>",
        "UPDATE t_issue_notification SET deleted = 1 WHERE 1=1",
        "<if test='params.receiverId != null'>",
        " AND receiver_id = #{params.receiverId}",
        "</if>",
        "<if test='params.isRead != null'>",
        " AND is_read = #{params.isRead}",
        "</if>",
        "</script>"
    })
    int batchDeleteByCondition(@Param("params") Map<String, Object> params);
    
    /**
     * 根据接收者ID统计总数
     */
    @Select("SELECT COUNT(*) FROM t_issue_notification WHERE receiver_id = #{receiverId} AND deleted = 0")
    int countByReceiverId(@Param("receiverId") Long receiverId);
    
    /**
     * 根据接收者ID统计未读数
     */
    @Select("SELECT COUNT(*) FROM t_issue_notification WHERE receiver_id = #{receiverId} AND is_read = 0 AND deleted = 0")
    int countUnreadByReceiverId(@Param("receiverId") Long receiverId);

    /**
     * 根据ID查询通知
     */
    @Select("SELECT * FROM t_issue_notification WHERE id = #{id}")
    IssueNotification selectById(@Param("id") Long id);

    /**
     * 插入通知
     */
    @Insert("INSERT INTO t_issue_notification (issue_id, receiver_id, notification_type, title, content, sender_id, is_read, create_time) " +
            "VALUES (#{issueId}, #{receiverId}, #{notificationType}, #{title}, #{content}, #{senderId}, #{isRead}, #{createTime})")
    int insert(IssueNotification notification);

    /**
     * 根据ID更新通知
     */
    @Update("UPDATE t_issue_notification SET issue_id = #{issueId}, receiver_id = #{receiverId}, notification_type = #{notificationType}, " +
            "title = #{title}, content = #{content}, sender_id = #{senderId}, is_read = #{isRead}, create_time = #{createTime} " +
            "WHERE id = #{id}")
    int updateById(IssueNotification notification);

    /**
     * 根据ID删除通知
     */
    @Update("UPDATE t_issue_notification SET deleted = true WHERE id = #{id}")
    int deleteById(@Param("id") Long id);
}